﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;
using System.Data.SqlClient;

namespace IHISCETeim.DAL
{
    public class AgentQltyDAL
    {
        /// <summary>
        /// 添加品情
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listImg"></param>
        /// <param name="listParts"></param>
        /// <returns></returns>
        public static int Add(AgentQltyInfo obj,List<AgentQltyImgInfo> listImg,List<AgentQltyPartsInfo> listParts )
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    int QltyId = 0;
                  //  DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_Add", obj.IssueDate, obj.SerialNumber, obj.MachineType, obj.CustomerName, obj.CustomerMobile, obj.IntoDate, obj.IntoAddress, obj.FaultDate, obj.WorkHour, obj.ReportUser, obj.ReportTel, obj.MacNature, obj.IntoConditi, obj.IntoSpecial, obj.DamagePartsNumber, obj.FaultDescription, obj.FaultReason, obj.ProcessMode, obj.SvrResult, obj.SvrScheme, obj.IsVisit, obj.PartsSendDate, obj.PartsSendType, obj.UserId, obj.AgentName, obj.SrvActUserId, obj.SrvActRealName, obj.SrvMangerUserId, obj.ClaimState, obj.RejectReason, obj.CancelReason, obj.PartsAcceptAddress, obj.PartsAcceptName, obj.PartsAcceptTel,obj.PartsAcceptDate);
                    
                    /**/
                    DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_Add");
                    DB.AddInParameter(dbCommand, "ClaimsNumber", DbType.String, obj.ClaimsNumber);
                    DB.AddInParameter(dbCommand, "IssueDate", DbType.DateTime, obj.IssueDate);
                    DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
                    DB.AddInParameter(dbCommand, "MachineType", DbType.String, obj.MachineType);
                    DB.AddInParameter(dbCommand, "CustomerName", DbType.String, obj.CustomerName);
                    DB.AddInParameter(dbCommand, "CustomerMobile", DbType.String, obj.CustomerMobile);

                  //  DB.AddInParameter(dbCommand, "IntoDate", DbType.DateTime, obj.IntoDate);
                    SqlParameter _IntoDate = new SqlParameter("@IntoDate", SqlDbType.DateTime);
                    _IntoDate.IsNullable = true;
                    if (obj.IntoDate == DateTime.MinValue)
                        _IntoDate.Value = DBNull.Value;
                    else
                        _IntoDate.Value = obj.IntoDate;
                    dbCommand.Parameters.Add(_IntoDate);

                    DB.AddInParameter(dbCommand, "IntoAddress", DbType.String, obj.IntoAddress);
                    DB.AddInParameter(dbCommand, "FaultDate", DbType.DateTime, obj.FaultDate);
                    DB.AddInParameter(dbCommand, "WorkHour", DbType.Decimal, obj.WorkHour);
                    DB.AddInParameter(dbCommand, "ReportUser", DbType.String, obj.ReportUser);
                    DB.AddInParameter(dbCommand, "ReportTel", DbType.String, obj.ReportTel);
                    DB.AddInParameter(dbCommand, "MacNature", DbType.String, obj.MacNature);
                    DB.AddInParameter(dbCommand, "IntoConditi", DbType.String, obj.IntoConditi);
                    DB.AddInParameter(dbCommand, "IntoSpecial", DbType.String, obj.IntoSpecial);
                    DB.AddInParameter(dbCommand, "DamagePartsNumber", DbType.String, obj.DamagePartsNumber);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "FaultReason", DbType.String, obj.FaultReason);
                    DB.AddInParameter(dbCommand, "ProcessMode", DbType.String, obj.ProcessMode);
                    DB.AddInParameter(dbCommand, "SvrResult", DbType.String, obj.SvrResult);
                    DB.AddInParameter(dbCommand, "SvrScheme", DbType.String, obj.SvrScheme);
                    DB.AddInParameter(dbCommand, "IsVisit", DbType.Boolean, obj.IsVisit);
                    DB.AddInParameter(dbCommand, "PartsSendDate", DbType.String, obj.PartsSendDate);
                    DB.AddInParameter(dbCommand, "PartsSendType", DbType.String, obj.PartsSendType);
                    DB.AddInParameter(dbCommand, "UserId", DbType.Int32, obj.UserId);
                    DB.AddInParameter(dbCommand, "AgentName", DbType.String, obj.AgentName);
                    DB.AddInParameter(dbCommand, "SrvActUserId", DbType.Int32, obj.SrvActUserId);
                    DB.AddInParameter(dbCommand, "SrvActRealName", DbType.String, obj.SrvActRealName);
                    DB.AddInParameter(dbCommand, "SrvMangerUserId", DbType.Int32, obj.SrvMangerUserId);
                    DB.AddInParameter(dbCommand, "ClaimState", DbType.String, obj.ClaimState);
                    DB.AddInParameter(dbCommand, "RejectReason", DbType.String, obj.RejectReason);
                    DB.AddInParameter(dbCommand, "CancelReason", DbType.String, obj.CancelReason);
                    DB.AddInParameter(dbCommand, "PartsAcceptAddress", DbType.String, obj.PartsAcceptAddress);
                    DB.AddInParameter(dbCommand, "PartsAcceptName", DbType.String, obj.PartsAcceptName);
                    DB.AddInParameter(dbCommand, "PartsAcceptTel", DbType.String, obj.PartsAcceptTel);
                    DB.AddInParameter(dbCommand, "PartsAcceptDate", DbType.String, obj.PartsAcceptDate);
                    /**/
                 //string XX=   DB.ExecuteScalar(dbCommand, trans).ToString();

                    QltyId= Convert.ToInt32(DB.ExecuteScalar(dbCommand,trans));

                    foreach (AgentQltyImgInfo itm in listImg)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("AgentQltyImg_Add",QltyId,itm.Title,itm.IMGAbsolutePath,itm.IMGRelatively,itm.FileIndex,itm.FromType);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    foreach (AgentQltyPartsInfo itm in listParts)
                    {
                        DbCommand dbCommandParts = DB.GetStoredProcCommand("AgentQltyParts_Add", QltyId, itm.ItemCode, itm.Dscription, itm.Quantity, itm.PartsDemand, itm.LastPurPrice, itm.Remark,itm.InStock,itm.IsSPZGL);
                        DB.ExecuteScalar(dbCommandParts, trans);
                    }

                    //SEMailListDAL.Add(objEmail, DB, trans);
                   // trans.Rollback();
                    trans.Commit();
                    return QltyId;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}","AgentQltyDAL.Add",exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return 0;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 根据品情号获取其附属的品情号
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static string GetNewClaimsNumber(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetNewClaimsNumber", ClaimsNumber);
            return DB.ExecuteScalar(dbCommand).ToString();
        }

        public static DataTableCollection GetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }


        /// <summary>
        /// 删除品情
        /// </summary>
        /// <param name="QltyId"></param>
        /// <returns></returns>
        public static bool Delete(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_Delete", QltyId);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="QltyId"></param>
        /// <returns></returns>
        public static IDataReader GetByQltyId(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetByQltyId", QltyId);
            return DB.ExecuteReader(dbCommand);
        }
        /// <summary>
        /// 根据品情号获取信息
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static IDataReader GetByClaimsNumber(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetByClaimsNumber", ClaimsNumber);
            return DB.ExecuteReader(dbCommand);
        }
        /// <summary>
        /// 获取上传片
        /// </summary>
        /// <param name="QltyId"></param>
        /// <returns></returns>
        public static IDataReader GetAllImgByQltyId(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetAllImgByQltyId", QltyId);
            return DB.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// 获取配件 包括物流信息
        /// </summary>
        /// <param name="QltyId"></param>
        /// <returns></returns>
        public static DataTable GetAllPartsAndLogisticsByQltyId(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetAllPartsByQltyId", QltyId);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取配件
        /// </summary>
        /// <param name="QltyId"></param>
        /// <returns></returns>
        public static IDataReader GetAllPartsByQltyId(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetAllPartsByQltyId", QltyId);
            return DB.ExecuteReader(dbCommand);
        }
        /// <summary>
        /// 根据品情号码查找所有配件
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static DataTable GetAllPartsByClaimsNumber(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetAllPartsByClaimsNumber", ClaimsNumber);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 根据用户，获取最后一次配件收件信息
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public static DataTable GetLastPartsAcceptInfo(int UserId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetLastPartsAcceptInfo", UserId);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listImg"></param>
        /// <param name="listParts"></param>
        /// <returns></returns>
        public static bool SvrActEdit(AgentQltyRejectInfo rejectObj,AgentQltyInfo obj, List<AgentQltyImgInfo> listImg, List<AgentQltyPartsInfo> listParts,List<SEMailListInfo> objEmails)
	    {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //删除原有配件及图信息
                    DbCommand dbCommandDELETEIMG = DB.GetStoredProcCommand("AgentQlty_DeleteImgByQltyId", obj.QltyId);
                    DB.ExecuteScalar(dbCommandDELETEIMG, trans);

                    DbCommand dbCommandDELETEParts = DB.GetStoredProcCommand("AgentQlty_DeletePartsByQltyId", obj.QltyId);
                    DB.ExecuteScalar(dbCommandDELETEParts, trans);

                    //int QltyId = 0;
                   // DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_SvrActEdit", obj.QltyId, obj.IssueDate, obj.SerialNumber, obj.CustomerMobile, obj.MachineType, obj.CustomerName, obj.IntoDate, obj.IntoAddress, obj.FaultDate, obj.WorkHour, obj.ReportUser, obj.ReportTel, obj.MacNature, obj.IntoConditi, obj.IntoSpecial, obj.DamagePartsNumber, obj.FaultDescription, obj.FaultReason, obj.ProcessMode, obj.ReportType, obj.SvrResult, obj.SvrScheme, obj.IsVisit, obj.PartsSendDate, obj.PartsSendType, obj.ClaimState, obj.IsCancelReason, obj.CancelReason, obj.SrvMangerUserId, obj.RejectReason, obj.MainFaultID, obj.SrvActReplyDate, obj.SrvMangerReplyDate, obj.NoticeSendPartsDate, obj.PartsRequestType, obj.PartsAcceptAddress, obj.PartsAcceptName, obj.PartsAcceptTel);

                    

                    DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_SvrActEdit");
                    DB.AddInParameter(dbCommand, "QltyId", DbType.Int32, obj.QltyId);
                    DB.AddInParameter(dbCommand, "IssueDate", DbType.DateTime, obj.IssueDate);
                    DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
                    DB.AddInParameter(dbCommand, "CustomerMobile", DbType.String, obj.CustomerMobile);
                    DB.AddInParameter(dbCommand, "MachineType", DbType.String, obj.MachineType);
                    DB.AddInParameter(dbCommand, "CustomerName", DbType.String, obj.CustomerName);
                    //DB.AddInParameter(dbCommand, "IntoDate", DbType.DateTime, obj.IntoDate);

                    SqlParameter _IntoDate = new SqlParameter("@IntoDate", SqlDbType.DateTime);
                    _IntoDate.IsNullable = true;
                    if (obj.IntoDate == DateTime.MinValue)
                        _IntoDate.Value = DBNull.Value;
                    else
                        _IntoDate.Value = obj.IntoDate;
                    dbCommand.Parameters.Add(_IntoDate);

                    DB.AddInParameter(dbCommand, "IntoAddress", DbType.String, obj.IntoAddress);
                    DB.AddInParameter(dbCommand, "FaultDate", DbType.DateTime, obj.FaultDate);
                    DB.AddInParameter(dbCommand, "WorkHour", DbType.Decimal, obj.WorkHour);
                    DB.AddInParameter(dbCommand, "ReportUser", DbType.String, obj.ReportUser);
                    DB.AddInParameter(dbCommand, "ReportTel", DbType.String, obj.ReportTel);
                    DB.AddInParameter(dbCommand, "MacNature", DbType.String, obj.MacNature);
                    DB.AddInParameter(dbCommand, "IntoConditi", DbType.String, obj.IntoConditi);
                    DB.AddInParameter(dbCommand, "IntoSpecial", DbType.String, obj.IntoSpecial);
                    DB.AddInParameter(dbCommand, "DamagePartsNumber", DbType.String, obj.DamagePartsNumber);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "FaultReason", DbType.String, obj.FaultReason);
                    DB.AddInParameter(dbCommand, "ProcessMode", DbType.String, obj.ProcessMode);
                    DB.AddInParameter(dbCommand, "ReportType", DbType.String, obj.ReportType);
                    DB.AddInParameter(dbCommand, "SvrResult", DbType.String, obj.SvrResult);
                    DB.AddInParameter(dbCommand, "SvrScheme", DbType.String, obj.SvrScheme);
                    DB.AddInParameter(dbCommand, "IsVisit", DbType.Boolean, obj.IsVisit);
                    DB.AddInParameter(dbCommand, "PartsSendDate", DbType.String, obj.PartsSendDate);
                    DB.AddInParameter(dbCommand, "PartsSendType", DbType.String, obj.PartsSendType);
                    DB.AddInParameter(dbCommand, "ClaimState", DbType.String, obj.ClaimState);
                    DB.AddInParameter(dbCommand, "IsCancelReason", DbType.Boolean, obj.IsCancelReason);
                    DB.AddInParameter(dbCommand, "CancelReason", DbType.String, obj.CancelReason);
                    DB.AddInParameter(dbCommand, "SrvMangerUserId", DbType.Int32, obj.SrvMangerUserId);
                    DB.AddInParameter(dbCommand, "RejectReason", DbType.String, obj.RejectReason);
                    DB.AddInParameter(dbCommand, "MainFaultID", DbType.Int32, obj.MainFaultID);
                    #region 2014-08-25处理无法添加最小时间的问题
                    //DB.AddInParameter(dbCommand, "SrvActReplyDate", DbType.DateTime, obj.SrvActReplyDate);
                    SqlParameter _SrvActReplyDate = new SqlParameter("@SrvActReplyDate", SqlDbType.DateTime);
                    _SrvActReplyDate.IsNullable = true;
                    if (obj.SrvActReplyDate == DateTime.MinValue)
                        _SrvActReplyDate.Value = DBNull.Value;
                    else
                        _SrvActReplyDate.Value = obj.SrvActReplyDate;
                    dbCommand.Parameters.Add(_SrvActReplyDate);
                    //DB.AddInParameter(dbCommand, "SrvMangerReplyDate", DbType.DateTime, obj.SrvMangerReplyDate);
                    SqlParameter _SrvMangerReplyDate = new SqlParameter("@SrvMangerReplyDate", SqlDbType.DateTime);
                    _SrvMangerReplyDate.IsNullable = true;
                    if (obj.SrvMangerReplyDate == DateTime.MinValue)
                        _SrvMangerReplyDate.Value = DBNull.Value;
                    else
                        _SrvMangerReplyDate.Value = obj.SrvMangerReplyDate;
                    dbCommand.Parameters.Add(_SrvMangerReplyDate);
                    //DB.AddInParameter(dbCommand, "NoticeSendPartsDate", DbType.DateTime, obj.NoticeSendPartsDate);
                    SqlParameter _NoticeSendPartsDate = new SqlParameter("@NoticeSendPartsDate", SqlDbType.DateTime);
                    _NoticeSendPartsDate.IsNullable = true;
                    if (obj.NoticeSendPartsDate == DateTime.MinValue)
                        _NoticeSendPartsDate.Value = DBNull.Value;
                    else
                        _NoticeSendPartsDate.Value = obj.NoticeSendPartsDate;
                    dbCommand.Parameters.Add(_NoticeSendPartsDate);
                    #endregion
                    DB.AddInParameter(dbCommand, "PartsRequestType", DbType.String, obj.PartsRequestType);
                    DB.AddInParameter(dbCommand, "PartsAcceptAddress", DbType.String, obj.PartsAcceptAddress);
                    DB.AddInParameter(dbCommand, "PartsAcceptName", DbType.String, obj.PartsAcceptName);
                    DB.AddInParameter(dbCommand, "PartsAcceptTel", DbType.String, obj.PartsAcceptTel);
                    DB.AddInParameter(dbCommand, "PartsAcceptDate", DbType.String, obj.PartsAcceptDate);
                    DB.AddInParameter(dbCommand, "AgentSvrUserId", DbType.Int32, obj.AgentSvrUserId);
                    DB.AddInParameter(dbCommand, "AgentSvrRealName", DbType.String, obj.AgentSvrRealName);
                    DB.AddInParameter(dbCommand, "AgentSvrTel", DbType.String, obj.AgentSvrTel);
                    DB.AddInParameter(dbCommand, "IsNeedAppove", DbType.Boolean, obj.IsNeedAppove);
                    DB.AddInParameter(dbCommand, "ClaimsNumber", DbType.String, obj.ClaimsNumber);
                    DB.AddInParameter(dbCommand, "CCPartEmail", DbType.String, obj.CCPartEmail);
                    DB.AddInParameter(dbCommand, "IsMTTR", DbType.Boolean, obj.IsMTTR);
                    DB.AddInParameter(dbCommand, "IsYJGL", DbType.Boolean, obj.IsYJGL);
                    DB.AddInParameter(dbCommand, "IsOldConfirm", DbType.Boolean, obj.IsOldConfirm);
                    DB.AddInParameter(dbCommand, "NoNormative", DbType.Boolean, obj.NoNormative);
                    DB.ExecuteScalar(dbCommand, trans);  

                    foreach (AgentQltyImgInfo itm in listImg)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("AgentQltyImg_Add", obj.QltyId, itm.Title, itm.IMGAbsolutePath, itm.IMGRelatively, itm.FileIndex, itm.FromType);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    foreach (AgentQltyPartsInfo itm in listParts)
                    {
                        DbCommand dbCommandParts = DB.GetStoredProcCommand("AgentQltyParts_Add", obj.QltyId, itm.ItemCode, itm.Dscription, itm.Quantity, itm.PartsDemand, itm.LastPurPrice,itm.Remark,itm.InStock,itm.IsSPZGL);
                        DB.ExecuteScalar(dbCommandParts, trans);
                    }
                    //2014-09-22 处理品情驳回记录
                    if(rejectObj!=null)
                    { 
                        DbCommand dbRejectCommand = DB.GetStoredProcCommand("AgentQltyReject_Add");
                        DB.AddInParameter(dbRejectCommand, "QltyId", DbType.Int32, obj.QltyId);
                        DB.AddInParameter(dbRejectCommand, "RejectDate", DbType.DateTime, rejectObj.RejectDate);
                        DB.AddInParameter(dbRejectCommand, "RejectReason", DbType.String, rejectObj.RejectReason);
                        DB.AddInParameter(dbRejectCommand, "RejectRealName", DbType.String, rejectObj.RejectRealName);
                        DB.AddInParameter(dbRejectCommand, "FromType", DbType.Int32, rejectObj.FromType);
                        DB.AddInParameter(dbRejectCommand, "TelRemark", DbType.String, rejectObj.TelRemark);

                        DB.ExecuteScalar(dbRejectCommand,trans);
                    }
                    //2014-07-26 需要处理一周回访
                    //if (obj.IsVisit && obj.ClaimState == "品情已确认")
                    //{
                    //    //TODO:By yyx 故障一周回访代码处理
                    //    DateTime VisitDate = DateTime.Now.AddDays(7);//默认为确认品情后7天内要回访
                    //    DbCommand dbCommandVisit = DB.GetStoredProcCommand("AgentQltyVisit_Add", obj.QltyId, obj.AgentName, DateTime.Now, VisitDate);
                    //    DB.ExecuteScalar(dbCommandVisit,trans);
                    //}
                    if(objEmails!=null && objEmails.Count>0)
                    {
                        foreach (SEMailListInfo objEmail in objEmails)
                        { 
                            DbCommand emaildbCommand = DB.GetStoredProcCommand("SEMailList_AddByType", objEmail.MailTo, objEmail.Subject, objEmail.Body,objEmail.EmailType);
                            DB.ExecuteScalar(emaildbCommand, trans);
                        }

                        //SEMailListDAL.Add(objEmail, DB, trans);
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "AgentQltyDAL.SvrActEdit", exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }	
		
	    }
        /// <summary>
        /// 取消的品情重新提交
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listImg"></param>
        /// <param name="listParts"></param>
        /// <returns></returns>
        public static bool UpdateCancelQlty(AgentQltyInfo obj, List<AgentQltyImgInfo> listImg, List<AgentQltyPartsInfo> listParts, SEMailListInfo objEmail)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //删除原有配件及图信息
                    DbCommand dbCommandDELETEIMG = DB.GetStoredProcCommand("AgentQlty_DeleteImgByQltyId", obj.QltyId);
                    DB.ExecuteScalar(dbCommandDELETEIMG, trans);

                    DbCommand dbCommandDELETEParts = DB.GetStoredProcCommand("AgentQlty_DeletePartsByQltyId", obj.QltyId);
                    DB.ExecuteScalar(dbCommandDELETEParts, trans);

                    //int QltyId = 0;
                    // DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_SvrActEdit", obj.QltyId, obj.IssueDate, obj.SerialNumber, obj.CustomerMobile, obj.MachineType, obj.CustomerName, obj.IntoDate, obj.IntoAddress, obj.FaultDate, obj.WorkHour, obj.ReportUser, obj.ReportTel, obj.MacNature, obj.IntoConditi, obj.IntoSpecial, obj.DamagePartsNumber, obj.FaultDescription, obj.FaultReason, obj.ProcessMode, obj.ReportType, obj.SvrResult, obj.SvrScheme, obj.IsVisit, obj.PartsSendDate, obj.PartsSendType, obj.ClaimState, obj.IsCancelReason, obj.CancelReason, obj.SrvMangerUserId, obj.RejectReason, obj.MainFaultID, obj.SrvActReplyDate, obj.SrvMangerReplyDate, obj.NoticeSendPartsDate, obj.PartsRequestType, obj.PartsAcceptAddress, obj.PartsAcceptName, obj.PartsAcceptTel);



                    DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_SvrActEdit");
                    DB.AddInParameter(dbCommand, "QltyId", DbType.Int32, obj.QltyId);
                    DB.AddInParameter(dbCommand, "IssueDate", DbType.DateTime, obj.IssueDate);
                    DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
                    DB.AddInParameter(dbCommand, "CustomerMobile", DbType.String, obj.CustomerMobile);
                    DB.AddInParameter(dbCommand, "MachineType", DbType.String, obj.MachineType);
                    DB.AddInParameter(dbCommand, "CustomerName", DbType.String, obj.CustomerName);
                   // DB.AddInParameter(dbCommand, "IntoDate", DbType.DateTime, obj.IntoDate);

                    SqlParameter _IntoDate = new SqlParameter("@IntoDate", SqlDbType.DateTime);
                    _IntoDate.IsNullable = true;
                    if (obj.IntoDate == DateTime.MinValue)
                        _IntoDate.Value = DBNull.Value;
                    else
                        _IntoDate.Value = obj.IntoDate;
                    dbCommand.Parameters.Add(_IntoDate);

                    DB.AddInParameter(dbCommand, "IntoAddress", DbType.String, obj.IntoAddress);
                    DB.AddInParameter(dbCommand, "FaultDate", DbType.DateTime, obj.FaultDate);
                    DB.AddInParameter(dbCommand, "WorkHour", DbType.Decimal, obj.WorkHour);
                    DB.AddInParameter(dbCommand, "ReportUser", DbType.String, obj.ReportUser);
                    DB.AddInParameter(dbCommand, "ReportTel", DbType.String, obj.ReportTel);
                    DB.AddInParameter(dbCommand, "MacNature", DbType.String, obj.MacNature);
                    DB.AddInParameter(dbCommand, "IntoConditi", DbType.String, obj.IntoConditi);
                    DB.AddInParameter(dbCommand, "IntoSpecial", DbType.String, obj.IntoSpecial);
                    DB.AddInParameter(dbCommand, "DamagePartsNumber", DbType.String, obj.DamagePartsNumber);
                    DB.AddInParameter(dbCommand, "FaultDescription", DbType.String, obj.FaultDescription);
                    DB.AddInParameter(dbCommand, "FaultReason", DbType.String, obj.FaultReason);
                    DB.AddInParameter(dbCommand, "ProcessMode", DbType.String, obj.ProcessMode);
                    DB.AddInParameter(dbCommand, "ReportType", DbType.String, obj.ReportType);
                    DB.AddInParameter(dbCommand, "SvrResult", DbType.String, obj.SvrResult);
                    DB.AddInParameter(dbCommand, "SvrScheme", DbType.String, obj.SvrScheme);
                    DB.AddInParameter(dbCommand, "IsVisit", DbType.Boolean, obj.IsVisit);
                    DB.AddInParameter(dbCommand, "PartsSendDate", DbType.String, obj.PartsSendDate);
                    DB.AddInParameter(dbCommand, "PartsSendType", DbType.String, obj.PartsSendType);
                    DB.AddInParameter(dbCommand, "ClaimState", DbType.String, obj.ClaimState);
                    DB.AddInParameter(dbCommand, "IsCancelReason", DbType.Boolean, obj.IsCancelReason);
                    DB.AddInParameter(dbCommand, "CancelReason", DbType.String, obj.CancelReason);
                    DB.AddInParameter(dbCommand, "SrvMangerUserId", DbType.Int32, obj.SrvMangerUserId);
                    DB.AddInParameter(dbCommand, "RejectReason", DbType.String, obj.RejectReason);
                    DB.AddInParameter(dbCommand, "MainFaultID", DbType.Int32, obj.MainFaultID);
                    #region 2014-08-25处理无法添加最小时间的问题
                    //DB.AddInParameter(dbCommand, "SrvActReplyDate", DbType.DateTime, obj.SrvActReplyDate);
                    SqlParameter _SrvActReplyDate = new SqlParameter("@SrvActReplyDate", SqlDbType.DateTime);
                    _SrvActReplyDate.IsNullable = true;
                    if (obj.SrvActReplyDate == DateTime.MinValue)
                        _SrvActReplyDate.Value = DBNull.Value;
                    else
                        _SrvActReplyDate.Value = obj.SrvActReplyDate;
                    dbCommand.Parameters.Add(_SrvActReplyDate);
                    //DB.AddInParameter(dbCommand, "SrvMangerReplyDate", DbType.DateTime, obj.SrvMangerReplyDate);
                    SqlParameter _SrvMangerReplyDate = new SqlParameter("@SrvMangerReplyDate", SqlDbType.DateTime);
                    _SrvMangerReplyDate.IsNullable = true;
                    if (obj.SrvMangerReplyDate == DateTime.MinValue)
                        _SrvMangerReplyDate.Value = DBNull.Value;
                    else
                        _SrvMangerReplyDate.Value = obj.SrvMangerReplyDate;
                    dbCommand.Parameters.Add(_SrvMangerReplyDate);
                    //DB.AddInParameter(dbCommand, "NoticeSendPartsDate", DbType.DateTime, obj.NoticeSendPartsDate);
                    SqlParameter _NoticeSendPartsDate = new SqlParameter("@NoticeSendPartsDate", SqlDbType.DateTime);
                    _NoticeSendPartsDate.IsNullable = true;
                    if (obj.NoticeSendPartsDate == DateTime.MinValue)
                        _NoticeSendPartsDate.Value = DBNull.Value;
                    else
                        _NoticeSendPartsDate.Value = obj.NoticeSendPartsDate;
                    dbCommand.Parameters.Add(_NoticeSendPartsDate);
                    #endregion
                    DB.AddInParameter(dbCommand, "PartsRequestType", DbType.String, obj.PartsRequestType);
                    DB.AddInParameter(dbCommand, "PartsAcceptAddress", DbType.String, obj.PartsAcceptAddress);
                    DB.AddInParameter(dbCommand, "PartsAcceptName", DbType.String, obj.PartsAcceptName);
                    DB.AddInParameter(dbCommand, "PartsAcceptTel", DbType.String, obj.PartsAcceptTel);
                    DB.AddInParameter(dbCommand, "PartsAcceptDate", DbType.String, obj.PartsAcceptDate);
                    DB.AddInParameter(dbCommand, "AgentSvrUserId", DbType.Int32, obj.AgentSvrUserId);
                    DB.AddInParameter(dbCommand, "AgentSvrRealName", DbType.String, obj.AgentSvrRealName);
                    DB.AddInParameter(dbCommand, "AgentSvrTel", DbType.String, obj.AgentSvrTel);
                    DB.AddInParameter(dbCommand, "IsNeedAppove", DbType.Boolean, obj.IsNeedAppove);
                    DB.AddInParameter(dbCommand, "ClaimsNumber", DbType.String, obj.ClaimsNumber);
                    DB.AddInParameter(dbCommand, "CCPartEmail", DbType.String, obj.CCPartEmail);
                    DB.AddInParameter(dbCommand, "IsMTTR", DbType.Boolean, obj.IsMTTR);
                    DB.AddInParameter(dbCommand, "IsYJGL", DbType.Boolean, obj.IsYJGL);
                    DB.AddInParameter(dbCommand, "IsOldConfirm", DbType.Boolean, obj.IsOldConfirm);
                    DB.AddInParameter(dbCommand, "NoNormative", DbType.Boolean, obj.NoNormative);
                    DB.ExecuteScalar(dbCommand, trans);

                    foreach (AgentQltyImgInfo itm in listImg)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("AgentQltyImg_Add", obj.QltyId, itm.Title, itm.IMGAbsolutePath, itm.IMGRelatively, itm.FileIndex, itm.FromType);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    foreach (AgentQltyPartsInfo itm in listParts)
                    {
                        DbCommand dbCommandParts = DB.GetStoredProcCommand("AgentQltyParts_Add", obj.QltyId, itm.ItemCode, itm.Dscription, itm.Quantity, itm.PartsDemand, itm.LastPurPrice, itm.Remark,itm.InStock,itm.IsSPZGL);
                        DB.ExecuteScalar(dbCommandParts, trans);
                    }
                    if (objEmail != null)
                    {
                        DbCommand emaildbCommand = DB.GetStoredProcCommand("SEMailList_AddByType", objEmail.MailTo, objEmail.Subject, objEmail.Body,objEmail.EmailType);
                        DB.ExecuteScalar(emaildbCommand, trans);

                        //SEMailListDAL.Add(objEmail, DB, trans);
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "AgentQltyDAL.UpdateCancelQlty", exc.Message ));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }

        }


        public static object GetERPSendDate(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetERPSendDate", QltyId);
            return DB.ExecuteScalar(dbCommand);
        }
        public static bool UpdatePartsSendDate(int QltyId, DateTime SupperSendDate, DateTime ERPSendDate, DateTime RealSendDate)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            //DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_UpdatePartsSendDate", QltyId, SupperSendDate, ERPSendDate, RealSendDate);
            try
            {
                DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_UpdatePartsSendDate");

                DB.AddInParameter(dbCommand, "QltyId", DbType.Int32, QltyId);

                SqlParameter _SupperSendDate = new SqlParameter("@SupperSendDate", SqlDbType.DateTime);
                _SupperSendDate.IsNullable = true;
                if (SupperSendDate == DateTime.MinValue)
                    _SupperSendDate.Value = DBNull.Value;
                else
                    _SupperSendDate.Value = SupperSendDate;

                dbCommand.Parameters.Add(_SupperSendDate);

                SqlParameter _ERPSendDate = new SqlParameter("@ERPSendDate", SqlDbType.DateTime);
                _ERPSendDate.IsNullable = true;
                if (ERPSendDate == DateTime.MinValue)
                    _ERPSendDate.Value = DBNull.Value;
                else
                    _ERPSendDate.Value = ERPSendDate;

                dbCommand.Parameters.Add(_ERPSendDate);

                SqlParameter _RealSendDate = new SqlParameter("@RealSendDate", SqlDbType.DateTime);
                _RealSendDate.IsNullable = true;
                if (RealSendDate == DateTime.MinValue)
                    _RealSendDate.Value = DBNull.Value;
                else
                    _RealSendDate.Value = RealSendDate;

                dbCommand.Parameters.Add(_RealSendDate);

                DB.ExecuteScalar(dbCommand);


                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static bool UpdatePartsAcceptDate(int QltyId, DateTime AgentAcceptDate, DateTime AgentRecoveryDate, DateTime PartsInDate, string ImgHY, List<AgentQltyImgInfo> imgList)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_UpdatePartsAcceptDate");

                    DB.AddInParameter(dbCommand, "QltyId", DbType.Int32, QltyId);

                    SqlParameter _AgentAcceptDate = new SqlParameter("@AgentAcceptDate", SqlDbType.DateTime);
                    _AgentAcceptDate.IsNullable = true;
                    if (AgentAcceptDate == DateTime.MinValue)
                        _AgentAcceptDate.Value = DBNull.Value;
                    else
                        _AgentAcceptDate.Value = AgentAcceptDate;

                    dbCommand.Parameters.Add(_AgentAcceptDate);

                    SqlParameter _AgentRecoveryDate = new SqlParameter("@AgentRecoveryDate", SqlDbType.DateTime);
                    _AgentRecoveryDate.IsNullable = true;
                    if (AgentRecoveryDate == DateTime.MinValue)
                        _AgentRecoveryDate.Value = DBNull.Value;
                    else
                        _AgentRecoveryDate.Value = AgentRecoveryDate;

                    dbCommand.Parameters.Add(_AgentRecoveryDate);

                    SqlParameter _PartsInDate = new SqlParameter("@PartsInDate", SqlDbType.DateTime);
                    _PartsInDate.IsNullable = true;
                    if (PartsInDate == DateTime.MinValue)
                        _PartsInDate.Value = DBNull.Value;
                    else
                        _PartsInDate.Value = PartsInDate;

                    dbCommand.Parameters.Add(_PartsInDate);

                    DB.AddInParameter(dbCommand, "ImgHY", DbType.String, ImgHY);


                    DB.ExecuteScalar(dbCommand,trans);

                    //删除原图信息
                    DbCommand dbCommandDELETEIMG = DB.GetStoredProcCommand("AgentQlty_DeleteImgByQltyId_1", QltyId);
                    DB.ExecuteScalar(dbCommandDELETEIMG, trans);

                    foreach (AgentQltyImgInfo itm in imgList)
                    {
                        DbCommand dbCommandIMG = DB.GetStoredProcCommand("AgentQltyImg_Add", QltyId, itm.Title, itm.IMGAbsolutePath, itm.IMGRelatively, itm.FileIndex, itm.FromType);
                        DB.ExecuteScalar(dbCommandIMG, trans);
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 品情 平均维修时间
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Sort"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTableCollection MTTRGetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyMTTR_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }

        /// <summary>
        /// 发货时间、到货时间及故障排除时间如果3天内未填入，是否可以每天发一份邮件跟催询问
        /// </summary>
        /// <param name="type">1.发货时间  2.到货时间 3.故障排除时间</param>
        /// <returns></returns>
        public static IDataReader GetWarnInputDate(int type)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetWarnInputDate", type);
            return DB.ExecuteReader(dbCommand);
        }

        public static DataTable AgentQltyReject_GetAllByQltyId(int QltyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyReject_GetAllByQltyId", QltyId);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 提交内部对策邮件发送
        /// </summary>
        /// <param name="QltyId"></param>
        /// <param name="CCPartEmail"></param>
        /// <param name="SvrScheme"></param>
        /// <returns></returns>
        public static int UpdateApproveScheme(int QltyId, string CCPartEmail, string SvrScheme,SEMailListInfo objEmail)
        {

             Database DB = DatabaseFactory.CreateDatabase();
             using (DbConnection conn = DB.CreateConnection())
             {
                 //打开连接
                 conn.Open();
                 //创建事务
                 DbTransaction trans = conn.BeginTransaction();
                 try
                 {
                     DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_UpdateApproveScheme", QltyId, CCPartEmail, SvrScheme);
                     int SendTag= Convert.ToInt32(DB.ExecuteScalar(dbCommand, trans));
                     if (SendTag == -1)
                         return -1;//表示已执行过发送,不能再发送

                     if (objEmail != null)
                     {
                        
                         DbCommand emaildbCommand = DB.GetStoredProcCommand("SEMailList_AddByType", objEmail.MailTo, objEmail.Subject, objEmail.Body,objEmail.EmailType);
                         DB.ExecuteScalar(emaildbCommand, trans);
                     }

                     trans.Commit();
                     return 1;
                 }
                 catch (Exception exc)
                 {
                     SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "AgentQltyDAL.UpdateApproveScheme", exc.Message));
                     //发生异常，事务回滚
                     trans.Rollback();
                     return 0;
                 }
                 finally
                 {
                     //关闭连接
                     conn.Close();
                 }
             }
           
        }

        public static DataTableCollection AgentQltyPartsGetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyParts_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }

        public static bool UpdateInStock(int ID, DateTime InDate, decimal InQuantity, string InOrderNum, string InRemark, decimal InWuPan, bool IsPan, int InGuiFan, string GoTrack, string GOTrackDate, SEMailListInfo objEmail)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {                   

                    DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyParts_UpdateInStock", ID, InDate, InQuantity, InOrderNum, InRemark, InWuPan, IsPan, InGuiFan, GoTrack, GOTrackDate);                   
                    DB.ExecuteScalar(dbCommand,trans);

                    if (objEmail != null)
                    {
                        DbCommand emaildbCommand = DB.GetStoredProcCommand("SEMailList_AddByType", objEmail.MailTo, objEmail.Subject, objEmail.Body,objEmail.EmailType);
                        DB.ExecuteScalar(emaildbCommand, trans);
                    }

                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    SEMailListDAL.AddAppError(string.Format("类方法:{0}<BR/> 错误:{1}", "AgentQltyDAL.UpdateInStock", exc.Message));
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }


        }



        /// <summary>
        /// 旧件返回规范
        /// </summary>
        /// <param name="f"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static DataTable GetInGuiFan(string f, string t)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyParts_GetInGuiFan", f, t);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        public static DataTable GetWuPaiBiLi(string f, string t)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyParts_GetWuPaiBiLi", f, t);
            return DB.ExecuteDataSet(dbCommand).Tables[0];

      }
        /// <summary>
        /// 索赔件自给率
        /// </summary>
        /// <param name="f"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static DataTable GetSPZGL(string f, string t)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQltyParts_GetSPZGL", f, t);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 2016-10-21 质管部 指派管制及对策管制
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool UpdateZLAssign(AgentQltyInfo obj)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_UpdateZLAssign");
                DB.AddInParameter(dbCommand, "QltyId", DbType.Int32, obj.QltyId);
                DB.AddInParameter(dbCommand, "ZLAssignUserId", DbType.Int32, obj.ZLAssignUserId);
                DB.AddInParameter(dbCommand, "ZLAssignRealName", DbType.String, obj.ZLAssignRealName);
                DB.AddInParameter(dbCommand, "ZLRank", DbType.String, obj.ZLRank);
                DB.AddInParameter(dbCommand, "ZLIsAfterSend", DbType.Boolean, obj.ZLIsAfterSend);


                SqlParameter _ZLAssignDate = new SqlParameter("@ZLAssignDate", SqlDbType.DateTime);
                _ZLAssignDate.IsNullable = true;
                if (obj.ZLAssignDate == DateTime.MinValue)
                    _ZLAssignDate.Value = DBNull.Value;
                else
                    _ZLAssignDate.Value = obj.ZLAssignDate;
                dbCommand.Parameters.Add(_ZLAssignDate);

                DB.AddInParameter(dbCommand, "ZLSituationReport", DbType.String, obj.ZLSituationReport);

                SqlParameter _ZLSituationDate = new SqlParameter("@ZLSituationDate", SqlDbType.DateTime);
                _ZLSituationDate.IsNullable = true;
                if (obj.ZLSituationDate == DateTime.MinValue)
                    _ZLSituationDate.Value = DBNull.Value;
                else
                    _ZLSituationDate.Value = obj.ZLSituationDate;
                dbCommand.Parameters.Add(_ZLSituationDate);

                int r = Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                return r > 0;
            }
            catch {
                return false;
            }

        }

        public static DataTable GetIsYJGLReport(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetIsYJGLReport", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        public static DataTable GetNoNormativeReport(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("AgentQlty_GetNoNormativeReport", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
    }
}
